package com.springboot.frame.backup;

import lombok.extern.slf4j.Slf4j;
import org.apache.logging.log4j.util.Strings;
import org.springframework.util.StringUtils;

import java.io.*;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author liheng
 * @ClassName ExportUtils
 * @Description
 * @date 2021-03-03 10:40
 */
@Slf4j
public class ExportUtils {

    /**
     * 创建导出文件
     *
     * @param backUpProperties
     * @return
     * @throws IOException
     */
    public static BufferedWriter mkdir(BackUpProperties backUpProperties) throws IOException {
        String folderBackup = backUpProperties.getExportPath();
        //文件名
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd@HH_mm_ss");
        String sqlFilename = String.format("%s-%s.sql", backUpProperties.getFileNamePrefix(), sdf.format(new Date()));
        File file = new File(backUpProperties.getExportPath());
        if (!file.exists()) {
            file.mkdirs();
        }
        BufferedWriter writer = new BufferedWriter(new FileWriter(folderBackup + "/" + sqlFilename));
        return writer;
    }


    /**
     * 导出文件头内容
     *
     * @param backUpProperties
     * @return
     * @throws IOException
     */
    public static BufferedWriter headContent(BackUpProperties backUpProperties) throws IOException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd@HH_mm_ss");
        BufferedWriter writer = mkdir(backUpProperties);
        writer.write("/*");
        writer.newLine();
        writer.write(" Data Info:");
        writer.newLine();
        writer.write(" Source Server Type    : MySQL");
        writer.newLine();
        writer.write(" Source Host           : " + backUpProperties.getHost() + ":" + backUpProperties.getPort());
        writer.newLine();
        writer.write(" Source Schema         : " + backUpProperties.getExportDatabaseName());
        writer.newLine();
        writer.write(" File Encoding         : UTF-8");
        writer.newLine();
        writer.write(" Date: " + sdf.format(new Date()));
        writer.newLine();
        writer.write("*/");
        writer.write("\n");
        writer.newLine();
        writer.write("\n");
        writer.newLine();
        writer.write("SET NAMES utf8mb4;");
        writer.newLine();
        writer.write("SET FOREIGN_KEY_CHECKS = 0;");
        writer.newLine();
        writer.flush();
        return writer;
    }

    /**
     * 获取数据库名称
     *
     * @param conn
     * @return
     */
    public static String getDbName(Connection conn) {
        try {
            String dbName = conn.getCatalog();
            return dbName;
        } catch (SQLException e) {
            throw new RuntimeException("无法获取数据库连接！");
        }
    }

    /**
     * 导出
     *
     * @param backUpProperties
     * @param conn
     */
    public static void export(BackUpProperties backUpProperties, Connection conn) {
        String dbName = getDbName(conn);
        try {
            // 头内容
            BufferedWriter writer = headContent(backUpProperties);
            if (TableType.all.equals(backUpProperties.getTableType())) {
                exportTable(dbName, backUpProperties, conn, writer);
                exportView(dbName, backUpProperties, conn, writer);
                exportProcedure(dbName, conn, writer);
            } else if (TableType.table.equals(backUpProperties.getTableType())) {
                exportTable(dbName, backUpProperties, conn, writer);
            } else if (TableType.view.equals(backUpProperties.getTableType())) {
                exportView(dbName, backUpProperties, conn, writer);
            } else {
                throw new RuntimeException("未知的导出表类型");
            }
            // 尾内容
            writer.newLine();
            writer.write("SET FOREIGN_KEY_CHECKS = 1;");
            writer.flush();
            writer.close();
            if (null != conn) {
                conn.close();
            }
        } catch (SQLException | IOException e) {
            throw new RuntimeException(e.getMessage());
        } finally {
            delBackUpFile(backUpProperties.getExportPath(), backUpProperties.getMaxHistory());
        }
    }

    /**
     * 导出表
     *
     * @param dbName
     * @param backUpProperties
     * @param conn
     * @param writer
     * @throws IOException
     * @throws SQLException
     */
    public static void exportTable(String dbName, BackUpProperties backUpProperties, Connection conn, BufferedWriter writer) throws IOException, SQLException {
        Statement stmtInfo = conn.createStatement();
        ResultSet rsInfo = stmtInfo.executeQuery(String.format("SHOW FULL TABLES FROM `%s` WHERE TABLE_TYPE = 'BASE TABLE'", dbName));
        // 遍历所有表
        String tableName;
        while (rsInfo.next()) {
            tableName = rsInfo.getString(1);
            // 导出表
            exportTableData(conn, tableName, writer, backUpProperties.isBulk());
        }    //end for tables
        if (null != rsInfo) {
            rsInfo.close();
        }
        if (null != stmtInfo) {
            stmtInfo.close();
        }
    }

    /**
     * 导出视图
     *
     * @param dbName
     * @param backUpProperties
     * @param conn
     * @throws IOException
     * @throws SQLException
     */
    public static void exportView(String dbName, BackUpProperties backUpProperties, Connection conn, BufferedWriter writer) throws IOException, SQLException {
        Statement stmtInfo = conn.createStatement();
        ResultSet rsInfo = stmtInfo.executeQuery(String.format("SHOW FULL TABLES FROM `%s` WHERE TABLE_TYPE = 'VIEW'", dbName));
        // 遍历所有视图
        String viewName;
        while (rsInfo.next()) {
            viewName = rsInfo.getString(1);
            // 导出表
            exportViewData(conn, viewName, writer);
            // 导出视图
        }    //end for view
        if (null != rsInfo) {
            rsInfo.close();
        }
        if (null != stmtInfo) {
            stmtInfo.close();
        }
    }

    /**
     * 导出视图数据
     *
     * @param conn
     * @param viewName
     * @param writer
     */
    private static void exportViewData(Connection conn, String viewName, BufferedWriter writer) throws SQLException, IOException{
        /* 表结构 */
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(String.format("SHOW CREATE VIEW `%s`", viewName));
        if (!rs.next()) {
            return;
        }
        writer.newLine();
        writer.newLine();
        writer.write(String.format("/*VIEW structure for VIEW `%s` */", viewName));
        writer.newLine();
        writer.write(String.format("DROP VIEW IF EXISTS `%s`;", viewName));
        writer.newLine();
        writer.write(rs.getString(2) + ";");
        writer.newLine();
        if (null != rs) {
            rs.close();
        }
        if (null != stmt) {
            stmt.close();
        }
    }

    /**
     * 导出表数据
     *
     * @param conn
     * @param tableName 表名
     * @param writer
     * @param bulkFlag  是否将数据放在一起
     * @throws SQLException
     * @throws IOException
     */
    private static void exportTableData(Connection conn, String tableName, BufferedWriter writer, boolean bulkFlag) throws SQLException, IOException {
        /* 表结构 */
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(String.format("SHOW CREATE TABLE `%s`", tableName));
        if (!rs.next()) {
            return;
        }
        writer.newLine();
        writer.write("-- ----------------------------");
        writer.newLine();
        writer.write(String.format("-- Table structure for table `%s` ", tableName));
        writer.newLine();
        writer.write("-- ----------------------------");
        writer.newLine();
        writer.write(String.format("DROP TABLE IF EXISTS `%s`;", tableName));
        writer.newLine();
        writer.write(rs.getString(2) + ";");
        writer.newLine();
        if (null != rs) {
            rs.close();
        }
        if (null != stmt) {
            stmt.close();
        }

        /* 导出表数据 */
        // 先获取记录数
        stmt = conn.createStatement();
        rs = stmt.executeQuery(String.format("SELECT COUNT(1) FROM `%s`", tableName));
        int rowCount = rs.next() ? rs.getInt(1) : 0;
        if (0 >= rowCount) {
            writer.flush();
            return;
        }
        writer.write(String.format("-- Data for the table `%s` ", tableName));
        writer.newLine();

        stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(Integer.MIN_VALUE);
        stmt.setFetchDirection(ResultSet.FETCH_REVERSE);
        rs = stmt.executeQuery(String.format("SELECT * FROM `%s`", tableName));
        int colCount = 0;
        Object colValue = null;
        // 所有数据用","连接
        if (!bulkFlag) {
            while (rs.next()) {
                colCount = rs.getMetaData().getColumnCount();

                writer.write(String.format("INSERT INTO `%s` VALUES (", tableName));
                // 获取表每一列数据
                for (int j = 0; j < colCount; j++) {
                    if (j > 0) {
                        writer.write(',');
                    }
                    colValue = rs.getObject(j + 1);
                    if (null != colValue) {
                        writer.write(String.format("'%s'", escapeString(colValue.toString())));
                    } else {
                        writer.write("NULL");
                    }
                }    //end for one record columns
                writer.write(");");
                writer.newLine();
            }    //end for table records
        }
        // 每行数据独立分开
        else {
            ResultSetMetaData rsMetaData = null;
            int counter = 0;
            while (rs.next()) {
                ++counter;
                rsMetaData = rs.getMetaData();
                colCount = rsMetaData.getColumnCount();

                // 第一条记录，则列出列名
                if (1 == counter) {
                    writer.write(String.format("INSERT INTO `%s` (", tableName));
                    for (int i = 0; i < colCount; i++) {
                        if (i > 0) {
                            writer.write(",");
                        }
                        writer.append('`').append(rsMetaData.getColumnName(i + 1)).append('`');
                    }
                    writer.append(") VALUES ");
                }
                // 获取表每一列数据
                for (int j = 0; j < colCount; j++) {
                    writer.write((0 >= j) ? '(' : ',');
                    colValue = rs.getObject(j + 1);
                    if (null != colValue) {
                        writer.write(String.format("'%s'", escapeString(colValue.toString())));
                    } else {
                        writer.write("NULL");
                    }
                }    //end for one record columns
                // 是否是最后记录
                if (rowCount > counter) {
                    writer.write("),");
                } else {
                    writer.write(");");
                }

            }    //end for table records
        }
        writer.newLine();
        writer.write("\n");
        writer.flush();
        if (null != rs) {
            rs.close();
        }
        if (null != stmt) {
            stmt.close();
        }
    }

    /**
     * 导出存储过程信息
     *
     * @param dbName
     * @param conn
     * @param writer
     * @throws SQLException
     * @throws IOException
     */
    public static void exportProcedure(String dbName, Connection conn, BufferedWriter writer) throws SQLException, IOException {
        Statement stmtInfo = conn.createStatement();
        ResultSet rsInfo = stmtInfo.executeQuery(String.format("SELECT `SPECIFIC_NAME` from `INFORMATION_SCHEMA`.`ROUTINES` WHERE `ROUTINE_SCHEMA` = '%s' AND ROUTINE_TYPE = 'PROCEDURE'; ", dbName));
        String procName;
        ResultSet rsData = null;
        Statement stmtData = null;
        while (rsInfo.next()) {
            procName = rsInfo.getString(1);
            stmtData = conn.createStatement();
            rsData = stmtData.executeQuery(String.format("SHOW CREATE PROCEDURE `%s`", procName));
            if (!rsData.next()) {
                continue;
            }
            writer.newLine();
            writer.newLine();
            writer.write(String.format("/* Procedure structure for procedure `%s` */", procName));
            writer.newLine();
            writer.write(String.format("/*!50003 DROP PROCEDURE IF EXISTS  `%s` */;", procName));
            writer.newLine();
            writer.write("DELIMITER $$");
            writer.newLine();
            writer.append("/*!50003 ").append(rsData.getString(3)).append(" */$$");
            writer.newLine();
            writer.write("DELIMITER ;");
        }
        if (null != rsData) {
            rsData.close();
        }
        if (null != stmtData) {
            stmtData.close();
        }
        if (null != rsInfo) {
            rsInfo.close();
        }
        if (null != stmtInfo) {
            stmtInfo.close();
        }
    }


    /**
     * 判断内容是否需要进行转义
     *
     * @param x
     * @return
     */
    public static boolean isNeedEscape(String x) {
        boolean needsHexEscape = false;
        if (!StringUtils.hasLength(x)) {
            return needsHexEscape;
        }
        int stringLength = x.length();
        int i = 0;
        do {
            if (i >= stringLength) {
                break;
            }
            char c = x.charAt(i);
            switch (c) {
                case 0: // '\0'
                    needsHexEscape = true;
                    break;

                case 10: // '\n'
                    needsHexEscape = true;
                    break;

                case 13: // '\r'
                    needsHexEscape = true;
                    break;

                case 92: // '\\'
                    needsHexEscape = true;
                    break;

                case 39: // '\''
                    needsHexEscape = true;
                    break;

                case 34: // '"'
                    needsHexEscape = true;
                    break;

                case 26: // '\032'
                    needsHexEscape = true;
                    break;
            }
            if (needsHexEscape) {
                break;
            }
            i++;
        } while (true);
        return needsHexEscape;
    }

    /**
     * 对mysql字符进行转义
     *
     * @param x
     * @return
     */
    public static String escapeString(String x) {
        if (Strings.isBlank(x)) {
            return x;
        }
        if (!isNeedEscape(x)) {
            return x;
        }
        int stringLength = x.length();
        String parameterAsString = x;
        StringBuffer buf = new StringBuffer((int) ((double) x.length() * 1.1000000000000001D));
        // 可以指定结果前后追加单引号：'
        //buf.append('\'');
        for (int i = 0; i < stringLength; i++) {
            char c = x.charAt(i);
            switch (c) {
                default:
                    break;

                case 0: // '\0'
                    buf.append('\\');
                    buf.append('0');
                    continue;

                case 10: // '\n'
                    buf.append('\\');
                    buf.append('n');
                    continue;

                case 13: // '\r'
                    buf.append('\\');
                    buf.append('r');
                    continue;

                case 92: // '\\'
                    buf.append('\\');
                    buf.append('\\');
                    continue;

                case 39: // '\''
                    buf.append('\\');
                    buf.append('\'');
                    continue;

                case 34: // '"'
                    buf.append('\\');
                    buf.append('"');
                    continue;

                case 26: // '\032'
                    buf.append('\\');
                    buf.append('Z');
                    continue;

            }
            buf.append(c);
        }
        // 可以指定结果前后追加单引号：'
        //buf.append('\'');
        parameterAsString = buf.toString();
        return parameterAsString;
    }

    /**
     * 删除时间大于指定天的备份文件
     *
     * @param exportPath
     * @param maxHistory
     * @return 返回删除文件个数
     */
    public static int delBackUpFile(String exportPath, int maxHistory) {
        // 得到目录
        File file = new File(exportPath);
        // 获取文件列表
        File[] tempList = file.listFiles();
        int count = 0;
        for (int i = 0; i < tempList.length; i++) {
            // 如果是文件
            if (tempList[i].isFile()) {
                // 获得文件名
                String filename = tempList[i].getName();
                // 获得文件后缀
                String suffix = filename.substring(filename.lastIndexOf("."));
                // 只针对sql文件
                if (".sql".equals(suffix)) {
                    // 获取时间
                    long time = tempList[i].lastModified();
                    // 获取当前时间
                    long ctime = System.currentTimeMillis();
                    // 删除大于30天的文件
                    if (((ctime - time) / 1000 / 60 / 60 / 24) > maxHistory) {
                        // 删除文件
                        tempList[i].delete();
                        count++;
                    }
                }
            }
        }
        return count;
    }

    /**
     * 自定义构建 BackUpProperties
     * 根据属性文件的配置导出指定位置的指定数据库到指定位置
     * 注意：
     * 利用属性文件提供的配置来拼装命令语句 在拼装命令语句的时候有一点是需要注意的：一般我们在命令窗口直接使用命令来
     * 进行导出的时候可以简单使用“>”来表示导出到什么地方，即mysqldump -uusername -ppassword databaseName
     * > exportPath， 但在Java中这样写是不行的，它需要你用-r明确的指出导出到什么地方，如： mysqldump -uusername
     * -ppassword databaseName -r exportPath。
     *
     * @param backUpProperties
     */
    public static void DumpData(BackUpProperties backUpProperties) {
        // 校验属性
        if (!StringUtils.hasLength(backUpProperties.getExportDatabaseName())) {
            log.error("请配置导出的数据库名");
            return;
        }
        if (!StringUtils.hasLength(backUpProperties.getExportPath())) {
            log.error("请配置导出的数据库备份地址");
            return;
        }
        if (!StringUtils.hasLength(backUpProperties.getMysqldumpPath())) {
            log.error("请配置Mysqldump可执行文件的路径");
            return;
        }
        // 构造导出命令
        List<String> command = new ArrayList<>();
        // 用户名
        String username = backUpProperties.getUsername();
        // 用户密码
        String password = backUpProperties.getPassword();
        // 需要导出的数据库名
        String exportDatabaseName = backUpProperties.getExportDatabaseName();
        // 从哪个主机导出数据库，如果没有指定这个值，则默认取localhost
        String host = backUpProperties.getHost();
        // 使用的端口号
        String port = backUpProperties.getPort();
        File file = new File(backUpProperties.getExportPath());
        if (!file.exists()) {
            file.mkdirs();
        }
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd@HH_mm_ss");
        // 导出路径
        String exportPath = backUpProperties.getExportPath() + File.separator + backUpProperties.getFileNamePrefix() + sdf.format(new Date()) + ".sql";
        // 密码是用的小p，而端口是用的大P。
        command.add(backUpProperties.getMysqldumpPath());
        command.add("-u" + username);
        command.add("-p" + password);
        command.add("-h" + host);
        command.add("-P" + port);
        command.add(exportDatabaseName);
        command.add("-r");
        command.add(exportPath);
        ProcessBuilder builder = new ProcessBuilder(command);
        BufferedReader br = null;
        Process start = null;
        try {
            log.info("执行的导出命令为：{}", command.toString());
            start = builder.start();
            br = new BufferedReader(new InputStreamReader(start.getErrorStream()));
            // 保存输出结果流
            StringBuilder buf = new StringBuilder();
            String line;
            while ((line = br.readLine()) != null) {
                buf.append(line);
            }
            log.info("备份sql返回输出结果：{}", buf.toString());
        } catch (IOException e) {
            log.error("执行数据库导出命令失败：命令>:{}\n错误信息：{}", command.toString(), e.getMessage());
        } finally {
            if (br != null) {
                try {
                    br.close();
                } catch (IOException e) {
                    log.error("BufferedReader关闭异常：{}", e.getMessage());
                }
            }
            if (start != null) {
                start.destroy();
            }
            // 清除过期文件
            delBackUpFile(backUpProperties.getExportPath(), backUpProperties.getMaxHistory());
        }

    }
}
